<?php
/**
 * 数据库助手类
 */
class DbHelper extends YucyAbsHelper
{
    /**
     * 
     * 数据库类型
     * @var string
     */
    protected $dbType = null;
    
    /**
     * 
     * 是否永久连接
     * @var bool
     */
    protected $pconnect = false;
    
    /**
     * 
     * 是否显示调试信息
     * @var bool
     */
    public $debug = false;
    
    /**
     * 
     * 当前要执行的SQL语句
     * @var string
     */
    protected $strQuery = '';
    
    /**
     * 
     * 最后插入ID
     * @var int
     */
    protected $lastInsId = null;
    
    /**
     * 
     * 返回影响记录数
     * @var int
     */
    protected $affectRows = 0;
    
    /**
     * 
     * 错误信息
     * @var string
     */
    protected $error = '';
	
	/**
	 * 
	 * 要操作的数据信息
	 * @var array
	 */
	protected $data = array();
	
	
	/**
	 * 
	 * 要操作的字段
	 * @var array
	 */
	protected $field = array();
    
    /**
     * 
     * 查询表达式参数
     * @var array
     */
    protected $options = array();
    
    /**
     * 
     * 数据库连接ID
     * @var array
     */
    protected $linkId = array();
    
    /**
     * 
     * 当前连接ID
     * @var int
     */
    protected $currentLinkId = null;
    
    /**
     * 
     * 当前查询ID
     * @var int
     */
    protected $queryId = null;
    
    /**
     * 
     * 是否已经连接数据库
     * @var bool
     */
    protected $connected = false;
    
    /**
     * 
     * 数据库连接参数配置
     * @var array
     */
    protected $config = array();
    
    /**
     * 
     * SQL 执行时间记录
     * @var int
     */
    protected $beginTime;
    
    /**
     * 
     * 关系表达式
     * @var array
     */
    protected $comparison = array('eq'=>'=','neq'=>'!=','gt'=>'>','egt'=>'>=','lt'=>'<','elt'=>'<=','notlike'=>'NOT LIKE','like'=>'LIKE');
    
    /**
     * 
     * 查询表达式
     * @var string
     */
    protected $selectSql = 'SELECT%DISTINCT% %FIELDS% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT%';

    
    /**
     * 加载数据库
     * @access Public
     * @param Array $dbConfig 数据库配置信息 
	 格式：array('driver'=>'mysql','connect'=>array('default'=>array(array('username'=>'root','password'=>'root','hostname'=> 'localhost', 'hostport'=>'3306', 'database'=>'test', 'table_prefix'=>''))));
     * @return Db
     */
    public static function factory(array $dbConfig, $pconnect = false)
    {
    	static $obj_sign = array();
    	
    	if (!count($dbConfig)) throw new YucyException('NO_DB_CONFIG');
    	
    	$current_sign = md5(serialize($dbConfig));
    	if (isset($obj_sign[$current_sign])) return $obj_sign[$current_sign];
    	
        // 读取数据库配置
		if (empty($dbConfig['driver'])) throw new YucyException('NO_DB_DRIVER');

        // 数据库类型
        $db_driver = ucwords(strtolower($dbConfig['driver']));
        
        // 加载数据库驱动
        $db_class = $db_driver . 'Db';
        require_once 'Db' . DS . $db_class . '.php';
        
        // 检查驱动类
        if (!class_exists($db_class, false)) throw new YucyException('NOT_SUPPORT_DB: ' . $dbConfig['driver']);
		
        $obj_db = new $db_class($dbConfig);
        $obj_sign[$current_sign] = $obj_db;
		$obj_db->dbType = $db_driver;
		
		// 是否记录BUG
        YucyConfig::get('is_debug') && $obj_db->debug = true;
        
        $pconnect && $obj_db->pconnect = true;
        
        return $obj_db;
    }

    
    /**
     * 初始化数据库连接
     * @access protected
     * @param bool $master 主服务器
     * @return void
     */
    protected function initConnect($master=true)
	{
		if ($this->connected) return ;
		// 计算连接信息的个数
		$connect_num = count($this->config['connect']);
		if ($connect_num == 1) 
		{
			$this->currentLinkId = $this->connect($this->config['connect']['default']['0']);
			return;
		}
		
		if ($master)
		{
			$key = isset($this->config['connect']['master']) ? 'master' : 'default';
		}
		else 
		{
			$key = isset($this->config['connect']['slave']) ? 'slave' : 'default';
		}
		
		$connect_num = count($this->config['connect'][$key]);
		$current_link_id = floor(mt_rand(0, $connect_num-1));
		$this->currentLinkId = $this->connect($this->config['connect'][$key][$current_link_id]);
		return;
    }


    /**
     * 
     * 连接数据库
     * @param array $config
     * @param int $currentLinkId
     */
	public function connect($config, $linkId = 0) {}

    
    /**
     * 数据库调试 记录当前SQL
     * @access protected
     */
    protected function debug()
	{
		if (!$this->debug) return;
        // 记录操作结束时间
        $runtime = number_format(microtime(TRUE) - $this->beginTime, 6);
        Yucy::getInstance()->getHelper()->getLogger()->write(" RunTime:".$runtime."s SQL = ".$this->strQuery,YucyLogger::INFO);
    }
    
    
    /**
     * 
     * 设置要查询的字段，支持别名
     * @param string $field
     * @param string $alias
     */
    public function setField($field = "*", $alias = null)
    {
    	$this->options['field'][] = array('field'=>$field, 'alias'=>$alias);
    	return $this;
    }
    
    
    /**
     * 
     * 设置表名
     * @param string $tableName 表名
     * @param string $alias 别名
     */
    public function setTable($tableName, $alias = null)
    {
    	$this->options['table'][] = array('name'=>$tableName, 'alias'=>$alias);
    	return $this;
    }
    
    
    /**
     * 
     * 是否对记录进行唯一过滤
     * @param bool $isDistinct
     */
    public function setDistinct($isDistinct = true)
    {
    	$this->options['distinct'] = $isDistinct;
    	return $this;
    }
    
    
    /**
     * 
     * where条件 只支持AND逻辑
     * @param string $field
     * @param mixed $value
     * @param string $opt
     * @return object
     */
    public function setWhere($field, $value, $opt = 'eq', $logic = 'AND')
    {
    	$where = self::where($field, $value, $opt);
    	$where['logic'] = strtoupper($logic);
    	$this->options['where'][] = $where;
    	return $this;
    }
    
    
    /**
     * 
     * 添加逻辑为OR的WHERE
     */
    public function orWhere()
    {
    	$where['data'] = func_get_args();
    	$where['logic'] = 'OR';
    	$this->options['where'][] = $where;
    	return $this;
    }
    
    
    /**
     * 
     * 生成一个WHERE结构
     * @param string $field
     * @param mixed $value
     * @param string $opt
     * @return array
     */
    public function where($field, $value, $opt = 'eq')
    {
    	return array('field'=>$field, 'value'=>$value, 'opt'=>$opt);
    }
    
    
    /**
     * 
     * 设置order by
     * @param string $orderBy
     */
    public function setOrderBy($orderBy)
    {
    	$this->options['order'] = $orderBy;
    	return $this;
    }
    
    
    /**
     * 
     * 设置having
     * @param string $having
     */
    public function setHaving($having)
    {
    	$this->options['having'] = $having;
    	return $this;
    }
    
    
    /**
     * 
     * 设置group by
     * @param string $groupBy
     */
    public function setGroupBy($groupBy)
    {
    	$this->options['group'] = $groupBy;
    	return $this;
    }
    
    
    /**
     * 
     * 设置锁
     * @param bool $isLock
     */
    public function setLock($isLock = true)
    {
    	$this->options['lock'] = $isLock;
    	return $this;
    }
    
    
    /**
     * 
     * 设置limit
     * @param int|string $limit
     * @param int $offset
     */
    public function setLimit($limit = null, $offset = 0)
    {
    	if ($limit === null) return $this;
    	
    	$offset = (int)$offset;
    	$limit = (int)$limit;
    	
    	$this->options['limit'] = $offset . ', ' . $limit;
    	return $this;
    }
    

	/**
	 * 
	 * 连接查询
	 * @param string $tableName 表名
	 * @param string $alias 名的别名
	 * @param string$on 连接关系
	 */
    public function setJoin($tableName, $on, $alias = null)
	{
        $this->options['join'][] = array('table'=>$tableName, 'alias'=>$alias, 'on'=>$on);
        return $this;
    }


	/**
     * 设置要操作的数据
     * @param array $data 数据
     * @return Db
     */
    public function setData(array $data)
	{
		if (!$data || !is_array($data)) throw_exception('DATA_ERROR');
            
        $this->data = $data;
        return $this;
    }
    
    
    /**
     * 
     * 清空数据
     */
    public function clearData()
    {
    	$this->data = array();
    	$this->field = array();
    	return $this;
    }
    
    
    /**
     * 
     * 设置要操作的字段的值,用于字段更新
     * @param string $field 字段名
     * @param mixed $value 字段值
     * @param bool $isParseValue 是否要过滤字段值
     */
    public function setValue($field, $value, $isParseValue = true)
    {
    	$this->field[$field] = array('value'=>$value, 'is_parse_value'=>$isParseValue);
    	return $this;
    }
    
    
    /**
     * 
     * 合并数据
     */
    protected function getData()
    {
    	if (!empty($this->data))
    	{
	    	foreach ($this->data as $key=>$value)
	    	{
	    		if (isset($this->field[$key])) continue;
	    		$this->field[$key] = array('value'=>$value);
	    	}
    	}
    	
    	$data = $this->field;
    	
    	// 清掉数据
    	$this->clearData();
    	
    	return $data;
    }
    

    /**
     * 设置锁机制
     * @access protected
     * @return string
     */
    protected function parseLock($lock = false)
	{
        if(!$lock) return '';
        
        if ('ORACLE' == strtoupper($this->dbType)) return ' FOR UPDATE NOWAIT ';
        
        return ' FOR UPDATE ';
    }

    
    /**
     * set分析
     * @access protected
     * @param array $data
     * @return string
     */
    protected function parseSet(array $data)
	{
        foreach ($data as $key=>$val)
		{
			if (!isset($val['is_parse_value']) || $val['is_parse_value']) $val['value'] = $this->parseValue($val['value']);
			$set[] = $this->addSpecialChar($key).'='.$val['value'];
        }
        return ' SET ' . implode(',', $set);
    }

    
    /**
     * value分析
     * @access protected
     * @param mixed $value
     * @return string
     */
    protected function parseValue($value)
	{
		if (is_int($value))
		{
			$value = intval($value);
		}
		elseif (is_float($value)) 
		{
			$value = floatval($value);
		}
		else 
		{
			$value = '\''.$this->escape_string($value).'\'';
		}
		
		return $value;
    }

    
    /**
     * field分析
     * @access protected
     * @param mixed $fields
     * @return string
     */
    protected function parseField($fields = '')
	{
		if (empty($fields)) return '*';
		
		$str_field = '';
		// 处理字段
		if (is_array($fields))
		{
			foreach ($fields as $key=>$val)
			{
				$field[] = !empty($val['alias']) ? $this->addSpecialChar($val['field']).' AS '.$this->addSpecialChar($val['alias']) : $this->addSpecialChar($val['field']);
			}
			
			$str_field = implode(',', $field);
		}
		
		return empty($str_field) ? '*' : $str_field;
    }

    
    /**
     * table分析
     * @access protected
     * @param string $tables
     * @return string
     */
    protected function parseTable(array $tables)
	{
		$arr_table = array();
		foreach ($tables as $val)
		{
			if (isset($this->config['table_prefix']) && $this->config['table_prefix'])
			{
				$val['name'] = $this->config['table_prefix'] . $val['name'];
			}
			
			if ($val['alias'])
			{
				$arr_table[] = $this->addSpecialChar($val['name']) . " " . $this->addSpecialChar($val['alias']);
			}
			else 
			{
				$arr_table[] = $this->addSpecialChar($val['name']);
			}
		}
		
		if (empty($arr_table)) Yucy::getInstance()->getHelper()->throwException('NO_TABLE');
		
		return implode(',', $arr_table);
    }
    
    
    /**
     * 
     * where分析
     * @param mixed $where
     * @return string
     */
    protected function parseWhere($where)
    {
    	if (!is_array($where)) return '';
    	
    	$str_where = '';
    	foreach ($where as $val)
    	{
    		if ($str_where != '') $str_where .= ' AND ';
    		$str_where .= $this->parseLogicForWhere($val);
    	}
    	
    	return $str_where == '' ? '' : ' WHERE ' . $str_where;
    }
    
    
    protected function parseLogicForWhere($where)
    {
    	if (strtoupper($where['logic']) != 'OR') return $this->parseOptForWhere($where);
    	
    	$arr_where = array();
    	foreach ($where['data'] as $val)
    	{
    		$arr_where[] = $this->parseOptForWhere($val);
    	}
    		
    	return '(' . implode(' OR ', $arr_where) . ')';
    }
    
    
    protected function parseOptForWhere($val)
    {
    	$field = $this->addSpecialChar($val['field']);
    	$opt = strtoupper($val['opt']);
    	$value = $val['value'];
    	
    	if (preg_match('/^(EQ|NEQ|GT|EGT|LT|ELT|NOTLIKE|LIKE)$/i', $opt)) 
		{ 
			return $field.' '.$this->comparison[strtolower($opt)].' '.$this->parseValue($value);
		}
		
		// IN操作
    	if (preg_match('/IN/i', $opt))
		{
			if (is_array($value))
			{
				$_value = array();
				foreach ($value as $key=>$val)
				{
					$_value[$key] = $this->parseValue($val);
				}
				$value = implode(',', $_value);
			}
			
			return $field . ' ' . strtoupper($opt) . ' ('.$value.')';
		}
    	
		// BETWEEN操作
		if (preg_match('/BETWEEN/i', $opt) && count($value) == 2)
		{
			return ' (' . $field . ' ' . strtoupper($opt) . ' ' . $this->parseValue(array_shift($value)) .' AND '.$this->parseValue(array_shift($value)).' )';
		}
		
		 throw new YucyException('NO_OPT:'.$opt);
    }


    /**
     * limit分析
     * @access protected
     * @param mixed $lmit
     * @return string
     */
    protected function parseLimit($limit)
	{
        return !empty($limit) ? ' LIMIT ' . $limit. ' ' : '';
    }
    

    /**
     * join分析
     * @access protected
     * @param mixed $join
     * @return string
     */
    protected function parseJoin($join) 
	{
		if (empty($join) || !is_array($join)) return '';
		
        $str_join = '';
        foreach ($join as $key=>$val)
        {
        	$str_join .= ' LEFT JOIN ' . $this->parseTable(array(array('name'=>$val['table'], 'alias'=>$val['alias']))) . ' ON ' . $this->addSpecialChar($val['on']);
        }
        
        return $str_join;
    }

    /**
     * order分析
     * @access protected
     * @param mixed $order
     * @return string
     */
    protected function parseOrder($order)
	{
        return !empty($order) ? ' ORDER BY ' . $order : '';
    }

    /**
     * group分析
     * @access protected
     * @param mixed $group
     * @return string
     */
    protected function parseGroup($group)
    {
        return !empty($group)? ' GROUP BY ' . $group : '';
    }

    /**
     * having分析
     * @access protected
     * @param string $having
     * @return string
     */
    protected function parseHaving($having)
    {
        return  !empty($having)? ' HAVING '. $having : '';
    }

    /**
     * distinct分析
     * @access protected
     * @param mixed $distinct
     * @return string
     */
    protected function parseDistinct($distinct)
	{
        return !empty($distinct)? ' DISTINCT ' : '';
    }
    

	/**
     * 分析表达式
     * @access private
     * @param array $options 表达式参数
     * @return array
     */
    protected function parseOptions($options = array())
	{
        if (!empty($options) && is_array($options))
        {
        	$options = array_merge($this->options, $options);
        }
        else 
        {
        	$options = $this->options;
        }
        
        // 清空sql表达式组装 避免影响下次查询
        $this->options = array();
       
        return $options;
    }


    /**
     * 字段和表名添加`
     * @access protected
     * @param mixed $value
     * @return mixed
     */
    protected function addSpecialChar(&$value)
	{
        if (0 === strpos(strtoupper($this->dbType), 'MYSQL'))
		{
            $value = trim($value);
            if ( false !== strpos($value, ' ') || false !== strpos($value, ',') || false !== strpos($value, '*') ||  false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`'))
			{
            }
			else
			{
                $value = '`'.$value.'`';
            }
        }
        return $value;
    }

    
    /**
     * 查询次数
     * @access public
     * @param mixed $times
     * @return void
     */
    public function Q($times='')
	{
        static $_times = 0;
        if (empty($times)) return $_times;

        $_times++;
        // 记录开始执行时间
        $this->beginTime = microtime(TRUE);
    }
    

    /**
     * 写入次数更新
     * @access public
     * @param mixed $times
     * @return void
     */
    public function W($times='')
	{
        static $_times = 0;
        if (empty($times)) return $_times;

        $_times++;
        // 记录开始执行时间
        $this->beginTime = microtime(TRUE);
    }
    

	/**
     * 返回最后插入的ID
     * @access public
     * @return string
     */
    public function getLastInsId()
	{
        return $this->lastInsId;
    }
    

    /**
     * 获取最近一次查询的sql语句
     * @access public
     * @return string
     */
    public function getLastSql()
	{
        return $this->strQuery;
    }
    
    
    /**
     * 
     * 插入一条记录
     */
    public function insert() {}
    
    
    /**
     * 
     * 更新一条记录
     */
    public function update() {}
    
    
    /**
     * 
     * 删除记录
     */
    public function delete() {}
    
    
    /**
     * 
     * 查询记录
     */
    public function select() {}
    

    /**
     * 获取最近的错误信息
     * @access public
     * @return string
     */
    public function getError()
	{
        return $this->error;
    }
    
    
    protected function escape_string($string)
    {
    	return $string;
    }
}
?>